package divestoclimb.checklist.storage;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

import divestoclimb.checklist.data.*;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

// TODO: Split up this class between database access methods and internal
// object-relational mapping methods (Cursors to objects and objects to
// ContentValues)
public class DbAdapter {

	private static final String TABLE_CATEGORY="category";
	public static final String KEY_CATEGORY_ID="_id";
	public static final String KEY_CATEGORY_NAME="Name";

	// This is a special category which is used as a default
	public static final long KEY_CATEGORY_ID_UNFILED=0;

	private static final String TABLE_TEMPLATE="template";
	public static final String KEY_TEMPLATE_ID="_id";
	public static final String KEY_TEMPLATE_NAME="Name";
	public static final String KEY_TEMPLATE_CATEGORY="Category";
	public static final String KEY_TEMPLATE_VERSION="Version";
	private static final String[] TEMPLATE_COLUMNS = new String[] { KEY_TEMPLATE_ID,
		KEY_TEMPLATE_NAME, KEY_TEMPLATE_CATEGORY, KEY_TEMPLATE_VERSION };

	// These constants are used to store the type of item for
	// records in table TEMPLATEITEM and CHECKLISTITEM
	public static final int ITEM_TYPE_CHECK=0;
	public static final int ITEM_TYPE_NOTE=1;

	// These constants are for determining the type of container for an item
	public static final int ITEM_CONTAINER_TEMPLATE=1;
	public static final int ITEM_CONTAINER_CHECKLIST=2;

	// The KEY_ITEM_* fields are defined for items in both checklists and templates
	private static final String TABLE_TEMPLATEITEM="templateitem";
	public static final String KEY_ITEM_ID="_id";
	public static final String KEY_TEMPLATEITEM_TEMPLATEID="Template";
	public static final String KEY_ITEM_ORDER="DisplayOrder";
	public static final String KEY_ITEM_TYPE="Type";
	public static final String KEY_ITEM_TEXT="Text";
	public static final String[] ITEM_COLUMNS = new String[] { KEY_ITEM_ID,
		KEY_TEMPLATEITEM_TEMPLATEID, KEY_ITEM_ORDER, KEY_ITEM_TYPE, KEY_ITEM_TEXT };

	private static final String TABLE_CHECKLIST="checklist";
	public static final String KEY_CHECKLIST_ID="_id";
	public static final String KEY_CHECKLIST_NAME="Name";
	public static final String KEY_CHECKLIST_CATEGORY="Category";
	public static final String KEY_CHECKLIST_WHENSTARTED="WhenStarted";
	public static final String KEY_CHECKLIST_WHENCOMPLETED="WhenCompleted";
	public static final String KEY_CHECKLIST_TEMPLATENAME="TemplateName";
	public static final String KEY_CHECKLIST_TEMPLATEVERSION="TemplateVersion";
	private static final String[] CHECKLIST_COLUMNS = new String[] { KEY_CHECKLIST_ID,
		KEY_CHECKLIST_NAME, KEY_CHECKLIST_CATEGORY, KEY_CHECKLIST_WHENSTARTED,
		KEY_CHECKLIST_WHENCOMPLETED, KEY_CHECKLIST_TEMPLATENAME,
		KEY_CHECKLIST_TEMPLATEVERSION };

	private static final String TABLE_CHECKLISTITEM="checklistitem";
	public static final String KEY_CHECKLISTITEM_CHECKLISTID="Checklist";
	public static final String KEY_CHECKLISTITEM_STATE="State";
	public static final String KEY_CHECKLISTITEM_WHENCHANGED="WhenChanged";
	private static final String[] CHECKLISTITEM_COLUMNS = new String[] { KEY_ITEM_ID,
		KEY_ITEM_ORDER, KEY_CHECKLISTITEM_CHECKLISTID, KEY_ITEM_TYPE, KEY_ITEM_TEXT,
		KEY_CHECKLISTITEM_STATE, KEY_CHECKLISTITEM_WHENCHANGED };

	/*private static final String TABLE_CHECKLISTFIELD="checklistfield";
	public static final String KEY_CHECKLISTFIELD_ID="_id";
	public static final String KEY_CHECKLISTFIELD_ITEMID="Item";
	public static final String KEY_CHECKLISTFIELD_WIDTH="Width";
	public static final String KEY_CHECKLISTFIELD_VALUE="Value";
	public static final String KEY_CHECKLISTFIELD_WHENSET="WhenSet";*/

	// This DateFormat is kept in the default time zone. It is used to parse Date
	// objects for display in local time.
	public static final DateFormat mDateFormatLocal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	// This DateFormat has its time zone changed to UTC. It is used to parse and
	// format Dates directly to and from the database.
	protected static final DateFormat mDateFormatGMT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	static {
		mDateFormatGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
	}

	// More internal variables
	private final Context mCtx;
    private DatabaseInit mDbHelper;
    private SQLiteDatabase mDb;

	// Constructor
	public DbAdapter(Context c) {
		mCtx=c;
	}

	/**
	 * Open the database. If it cannot be opened, try to create a new instance
	 * of the database. If it cannot be created, throw an exception to signal
	 * the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException if the database could be neither opened or created
	 */
	public DbAdapter open() throws SQLException {
		mDbHelper = new DatabaseInit(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * Gets an instance of a DateFormat object with a time zone set to GMT, for use
	 * in parsing and formatting dates for the database. To display a Date object
	 * to the user, use mDateFormatLocal instead. 
	 */
	public static DateFormat getDateFormatGMT() {
		return mDateFormatGMT;
	}

	/**
	 * Return a Cursor over the list of all defined categories, alphabetically
	 * sorted
	 * 
	 * @return Cursor over all categories
	 */
	public Cursor fetchAllCategories() {
		return mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_ID,
				KEY_CATEGORY_NAME }, null, null, null, null, KEY_CATEGORY_NAME);
	}

	/**
	 * Return a Category object for the Category matching cat_id
	 * 
	 * @param cat_id The ID of the Category to retrieve
	 * @return The Category object
	 */
	public Category fetchCategory(long cat_id) {
		String catidString=(new Long(cat_id)).toString();
		Cursor c = mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_NAME },
				KEY_CATEGORY_ID+"=?", new String[] { catidString }, null, null, null);
		Category cat;
		if(c.getCount() > 0) {
			c.moveToFirst();
			cat = new Category(cat_id, c.getString(c.getColumnIndexOrThrow(KEY_CATEGORY_NAME)));
		} else {
			cat = null;
		}
		c.close();
		return cat;
	}
	
	/**
	 * Return a category object for the Category with the matching name
	 * @param name The name of the Category to retrieve
	 * @return The Category object
	 */
	public Category fetchCategory(String name) {
		Cursor c = mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_ID },
				KEY_CATEGORY_NAME+"=?", new String[] { name }, null, null, null);
		Category cat;
		if(c.getCount() > 0) {
			c.moveToFirst();
			cat = new Category(c.getLong(c.getColumnIndexOrThrow(KEY_CATEGORY_ID)), name);
		} else {
			cat = null;
		}
		c.close();
		return cat;
	}
	
	/**
	 * Write a newly created Category to the database
	 * @param c the Category object to save
	 * @return The ID of the newly created record
	 */
	public long createCategory(Category c) {
		String name = c.getName();
		if(name.equals("") || fetchCategory(name) != null) {
			return -1;
		}
		return DbAdapter.createCategoryStatic(c, mDb);
	}

	/**
	 * Write a newly created Category c to database db. This method is the backend
	 * method behind createCategory and is also used by our DatabaseHelper. 
	 * @param c The Category object to create
	 * @param db The SQLiteDatabase reference
	 * @return The ID of the newly created record
	 */
	protected static long createCategoryStatic(Category c, SQLiteDatabase db) {
		ContentValues initialValues = new ContentValues();
		// Assigning an ID is only used when initially populating the database. Normal
		// save methods preclude this from happening normally.
		if(c.getID() != -1) {
			initialValues.put(KEY_CATEGORY_ID, c.getID());
		}
		initialValues.put(KEY_CATEGORY_NAME, c.getName());
		
		return db.insert(TABLE_CATEGORY, null, initialValues);
	}

	/**
	 * Update a Category object's record in the database
	 * For now, all this does is let you rename the category.
	 * @param c The Category whose record should be updated
	 * @return true if the operation succeeded, false otherwise
	 */
	public boolean updateCategory(Category c) {
		ContentValues newValues = new ContentValues();
		String name = c.getName();
		Category sameName = fetchCategory(name);
		if(name.equals("") || sameName != null && sameName.getID() != c.getID()) {
			return false;
		}
		newValues.put(KEY_CATEGORY_NAME, name);
		String idAsString=(new Long(c.getID())).toString();
		
		return mDb.update(TABLE_CATEGORY, newValues, KEY_CATEGORY_ID+"=?", new String[] { idAsString }) > 0;
	}

	/**
	 * Delete a category. Any templates or checklists in this category will be
	 * moved to the special Unfiled category. This function will never delete the
	 * Unfiled category itself because of this requirement.
	 * @param cat_id The ID of the category to delete
	 * @return true if the operation succeeded, false otherwise.
	 */
	public boolean deleteCategory(long cat_id) {
		// Refuse to delete the Unfiled category
		if(cat_id == KEY_CATEGORY_ID_UNFILED) {
			return false;
		}
		String idAsStringArray[] = { (new Long(cat_id)).toString() };
		// In order to delete a category, we have to move every Template and
		// Checklist in that category to the Unfiled category.
		// Templates
		ContentValues newCategory = new ContentValues();
		newCategory.put(KEY_TEMPLATE_CATEGORY, KEY_CATEGORY_ID_UNFILED);
		mDb.update(TABLE_TEMPLATE, newCategory, KEY_TEMPLATE_CATEGORY+"=?", idAsStringArray);
		
		// Checklists
		newCategory = new ContentValues();
		newCategory.put(KEY_CHECKLIST_CATEGORY, KEY_CATEGORY_ID_UNFILED);
		mDb.update(TABLE_CHECKLIST, newCategory, KEY_CHECKLIST_CATEGORY+"=?", idAsStringArray);

		// Now we can delete the category
		return mDb.delete(TABLE_CATEGORY, KEY_CATEGORY_ID+"=?", idAsStringArray) > 0;
	}

	/**
	 * Return a Cursor over the list of all templates in the database
	 * 
	 * @param sort The table key to sort results by
	 * @param category The Category ID to match, or -1 for templates in any category
	 * @return Cursor over all templates
	 */
	public Cursor fetchTemplates(String sort, long category) {
		String where = category != -1? KEY_TEMPLATE_CATEGORY + "=?": null;
		String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
		return mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, where, args,
				null, null, sort);
	}

	/**
	 * Build a Template object based on the record in the database with ID template_id
	 * @param template_id The template index in the database to base the Template on
	 * @return The Template object
	 */
	public Template fetchTemplate(long template_id) {
		String tempIdString=(new Long(template_id)).toString();
		Cursor c = mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, KEY_TEMPLATE_ID+"=?",
				new String[] { tempIdString }, null, null, null);
		Template t;
		if(c.getCount() > 0) {
			c.moveToFirst();
			t = fetchTemplateFromCursor(c);
		} else {
			t = null;
		}
		c.close();
		return t;
	}
	
	/**
	 * Build a Template object based on the record in the database with the given name
	 * @param name The name of the template in the database to base the Template on
	 * @return The Template object
	 */
	public Template fetchTemplate(String name) {
		Cursor c = mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, KEY_TEMPLATE_NAME+"=?",
				new String[] { name }, null, null, null);
		Template t;
		if(c.getCount() > 0) {
			c.moveToFirst();
			t = fetchTemplateFromCursor(c);
		} else {
			t = null;
		}
		c.close();
		return t;
	}
	
	/**
	 * Utility method to build a Template object from a record previously retrieved from
	 * the database.
	 * @param c The Cursor containing the Template fields, moved to the proper position
	 * @return A Template object representing the information at the current position of the cursor
	 */
	public Template fetchTemplateFromCursor(Cursor c) {
		// Now find out what category this template belongs to
		Category cat = fetchCategory(c.getInt(c.getColumnIndexOrThrow(KEY_TEMPLATE_CATEGORY)));

		Template t = new Template(c.getLong(c.getColumnIndexOrThrow(KEY_TEMPLATE_ID)), cat,
				c.getString(c.getColumnIndexOrThrow(KEY_TEMPLATE_NAME)),
				c.getString(c.getColumnIndexOrThrow(KEY_TEMPLATE_VERSION))
		);
		return t;
	}
	
	/**
	 * Map a Template object to a ContentValues object suitable for insert or
	 * update to the database.
	 * @param t The Template to map.
	 * @return A ContentValues object, or null if a database constraint would
	 * be violated as a result of this object's values.
	 */
	protected ContentValues getTemplateValues(Template t) {
		String name = t.getName();
		Template sameName = fetchTemplate(name);
		if(name.equals("") || sameName != null && sameName.getID() != t.getID()) {
			return null;
		}
		return getTemplateValuesStatic(t);
	}
	
	/**
	 * Map a Template object to a ContentValues object suitable for insert or
	 * update to the database. This method does not check for constraints because
	 * it is called statically and cannot do so.
	 * @param t The Template to map.
	 * @return A ContentValues object.
	 */
	protected static ContentValues getTemplateValuesStatic(Template t) {
		ContentValues v = new ContentValues();
		v.put(KEY_TEMPLATE_NAME, t.getName());
		v.put(KEY_TEMPLATE_VERSION, t.getVersion());
		// TODO remove for Database v6
		if(t.getCategory() == null) {
			v.put(KEY_TEMPLATE_CATEGORY, KEY_CATEGORY_ID_UNFILED);
		} else {
			v.put(KEY_TEMPLATE_CATEGORY, t.getCategory().getID());
		}
		return v;
	}

	/**
	 * Add a new template to the database
	 * @param t The Template object to populate the new record with
	 * @return The ID of the new template record or -1 if failed
	 */
	public long createTemplate(Template t) {
		ContentValues initialValues = getTemplateValues(t);
		if(initialValues == null) {
			return -1;
		}
		return mDb.insert(TABLE_TEMPLATE, null, initialValues);
	}

	/**
	 * Add a new template t to database db. Backend method for createTemplate and also
	 * used by our DatabaseHelper.
	 * @param t The Template object to populate the new record with
	 * @param db The SQLiteDatabase object referencing the database
	 * @return The ID of the new template record or -1 if failed
	 */
	protected static long createTemplateStatic(Template t, SQLiteDatabase db) {
		ContentValues initialValues = getTemplateValuesStatic(t);
		// Assigning an ID is only used when initially populating the database
		if(t.getID() != -1) {
			initialValues.put(KEY_TEMPLATE_ID, t.getID());
		}
		return db.insert(TABLE_TEMPLATE, null, initialValues);
	}

	/**
	 * Update a Template based on the current values in the object
	 * @param t The Template object to update in the database
	 * @return true if the Template was successfully updated, false otherwise
	 */
	public boolean updateTemplate(Template t) {
		ContentValues newValues = getTemplateValues(t);
		if(newValues == null) {
			return false;
		}
		String idAsString=(new Long(t.getID())).toString();

		return mDb.update(TABLE_TEMPLATE, newValues, KEY_TEMPLATE_ID+"=?", new String[] { idAsString }) > 0;
	}

	/**
	 * Delete a Template and all its items from the database
	 * @param template_id The ID of the template to delete 
	 * @return True if the operation succeeded, false otherwise
	 */
	public boolean deleteTemplate(long template_id) {
		String idAsStringArray[] = { (new Long(template_id)).toString() };
		// First delete all items belonging to this template
		mDb.delete(TABLE_TEMPLATEITEM, KEY_TEMPLATEITEM_TEMPLATEID+"=?", idAsStringArray);

		return mDb.delete(TABLE_TEMPLATE, KEY_TEMPLATE_ID+"=?", idAsStringArray) > 0;
	}

	/**
	 * Retrieve a Cursor of items belonging to the given container (e.g. Template or
	 * Checklist)
	 * This is a backend method used by ItemList--it should not be called from
	 * other classes. Use new ItemList() instead.
	 * @param container_type The type of container. ITEM_CONTAINER_TEMPLATE or ITEM_CONTAINER_CHECKLIST
	 * @param container_id The ID of the Template/Checklist
	 */
	public Cursor fetchItemsAsCursor(int container_type, long container_id) {
		String idString=(new Long(container_id)).toString();
		String table, where, columns[];
		if(container_type == ITEM_CONTAINER_TEMPLATE) {
			table=TABLE_TEMPLATEITEM;
			columns=ITEM_COLUMNS;
			where=KEY_TEMPLATEITEM_TEMPLATEID+"=?";
		} else {
			table=TABLE_CHECKLISTITEM;
			columns=CHECKLISTITEM_COLUMNS;
			where=KEY_CHECKLISTITEM_CHECKLISTID+"=?";
		}
		return mDb.query(table, columns,
				where, new String[] { idString },
				null, null, KEY_ITEM_ORDER);
	}

	public Item fetchTemplateItem(long id) {
		String itidString=(new Long(id)).toString();
		Cursor c = mDb.query(TABLE_TEMPLATEITEM, ITEM_COLUMNS, KEY_ITEM_ID+"=?", new String[] { itidString },
				null, null, null);
		Item i;
		if(c.getCount() > 0) {
			c.moveToFirst();
			i = fetchItemFromCursor(c);
		} else {
			i = null;
		}
		c.close();
		return i;
	}

	public Item fetchChecklistItem(long id) {
		String itidString=(new Long(id)).toString();
		Cursor c = mDb.query(TABLE_CHECKLISTITEM, CHECKLISTITEM_COLUMNS, KEY_ITEM_ID+"=?",
				new String[] { itidString }, null, null, null);
		Item i;
		if(c.getCount() > 0) {
			c.moveToFirst();
			i = fetchItemFromCursor(c);
		} else {
			i = null;
		}
		c.close();
		return i;
	}

	/**
	 * Builds an Item object of the appropriate type based on the data at the
	 * current position of a Cursor.
	 * @param c The Cursor to reference to build the Item
	 * @return The Item representing the data in the Cursor.
	 */
	public static Item fetchItemFromCursor(Cursor c) {
		// There are two independent conditions this method must deal with:
		// - Template or Checklist container
		// - Note or Check item type

		int idIdx=c.getColumnIndexOrThrow(KEY_ITEM_ID);
		int typeIdx=c.getColumnIndexOrThrow(KEY_ITEM_TYPE);
		int orderIdx=c.getColumnIndexOrThrow(KEY_ITEM_ORDER);
		int textIdx=c.getColumnIndexOrThrow(KEY_ITEM_TEXT);
		int containerIdx, type;

		if(c.getColumnIndex(KEY_CHECKLISTITEM_CHECKLISTID) != -1) {
			// This is an item from the checklist item table
			type=ITEM_CONTAINER_CHECKLIST;
			containerIdx=c.getColumnIndex(KEY_CHECKLISTITEM_CHECKLISTID);
		} else if(c.getColumnIndex(KEY_TEMPLATEITEM_TEMPLATEID) != -1) {
			// This is an item from the template item table
			type=ITEM_CONTAINER_TEMPLATE;
			containerIdx=c.getColumnIndexOrThrow(KEY_TEMPLATEITEM_TEMPLATEID);
		} else {
			return null;
		}

		// Now build the item based on the item type
		Item i;
		switch(c.getInt(typeIdx)) {
		case ITEM_TYPE_CHECK:
			if(type == ITEM_CONTAINER_CHECKLIST) {
				Date changedDate = null;
				try {
					String changedDateString;
					if((changedDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLISTITEM_WHENCHANGED))) == null) {
						changedDate = null;
					} else {
						// Dates are stored as GMT in the database. Parse using
						// mDateFormatGMT so the Date object is created properly.
						mDateFormatGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
						changedDate = mDateFormatGMT.parse(changedDateString);
					}
				} catch(ParseException e) {
					// Do nothing, not possible
				}
				i = new Check(type, c.getLong(containerIdx), c.getLong(idIdx),
						c.getInt(orderIdx), c.getString(textIdx),
						c.getInt(c.getColumnIndex(KEY_CHECKLISTITEM_STATE)),
						changedDate);
			} else {
				i = new Check(type, c.getLong(containerIdx), c.getLong(idIdx),
						c.getInt(orderIdx), c.getString(textIdx));
			}
			break;
		case ITEM_TYPE_NOTE:
			i = new Note(type, c.getLong(containerIdx), c.getLong(idIdx),
					c.getInt(orderIdx), c.getString(textIdx));
			break;
		default:
			// Error! not good
			i = null;
		}
		return i;
	}

	/**
	 * Map the Item i to a ContentValues structure for inserting or updating to
	 * the database.
	 * @param i The Item (either Note or Check) to read
	 * @return The ContentValues, or null if a validation error ocurred
	 */
	protected static ContentValues getItemValues(Item i) {
		ContentValues v = new ContentValues();
		String text = i.getText();
		if(text.equals("")) {
			return null;
		}
		v.put(KEY_ITEM_ORDER, i.getOrder());
		v.put(KEY_ITEM_TEXT, text);
		if(i instanceof Note) {
			v.put(KEY_ITEM_TYPE, ITEM_TYPE_NOTE);
		} else if(i instanceof Check) {
			v.put(KEY_ITEM_TYPE, ITEM_TYPE_CHECK);
		} else {
			// invalid argument passed to this function
			return null;
		}
		if(i.getContainerType() == ITEM_CONTAINER_CHECKLIST) {
			if(i instanceof Note) {
				// Dummy field to satisfy a constraint
				v.put(KEY_CHECKLISTITEM_STATE, 0);
			} else if(i instanceof Check) {
				v.put(KEY_CHECKLISTITEM_STATE, ((Check)i).getState());
				if(((Check)i).getStateChangedDate() == null) {
					v.putNull(KEY_CHECKLISTITEM_WHENCHANGED);
				} else {
					// Use mDateFormatGMT to format the Date as one in GMT for database
					// storage
					v.put(KEY_CHECKLISTITEM_WHENCHANGED, mDateFormatGMT.format(((Check)i).getStateChangedDate()));
				}
			}
		}
		return v;
	}

	/**
	 * Add a new template item to the database
	 * 
	 * @param i The Item object to populate the new record with
	 * @return The ID of the new Item record, or -1 if the creation fails
	 */
	public long createTemplateItem(Item i) {
		return createTemplateItemStatic(i, mDb);
	}

	public static long createTemplateItemStatic(Item i, SQLiteDatabase db) {
		ContentValues initialValues = getItemValues(i);
		if(initialValues == null) {
			return -1;
		}
		
		// Assigning an ID is only used when initially populating the database
		if(i.getID() != -1) {
			initialValues.put(KEY_ITEM_ID, i.getID());
		}
		initialValues.put(KEY_TEMPLATEITEM_TEMPLATEID, i.getContainerID());
		
		return db.insert(TABLE_TEMPLATEITEM, null, initialValues);
	}

	public long createChecklistItem(Item i) {
		ContentValues initialValues = getItemValues(i);
		if(initialValues == null) {
			return -1;
		}
		initialValues.put(KEY_CHECKLISTITEM_CHECKLISTID, i.getContainerID());

		return mDb.insert(TABLE_CHECKLISTITEM, null, initialValues);
	}

	/**
	 * Update an existing template item in the database
	 * 
	 * @param item The Item object to update. The database record will be matched using the ID of the Item
	 * @return true if the operation succeeded, false otherwise
	 */
	public boolean updateTemplateItem(Item item) {
		ContentValues newValues = getItemValues(item);
		if(newValues == null) {
			return false;
		}
		String idAsString=(new Long(item.getID())).toString();
		
		return mDb.update(TABLE_TEMPLATEITEM, newValues, KEY_ITEM_ID+"=?", new String[] { idAsString }) > 0;
	}

	public boolean updateChecklistItem(Item item) {
		ContentValues newValues = getItemValues(item);
		if(newValues == null) {
			return false;
		}
		String idAsString=(new Long(item.getID())).toString();

		boolean success = mDb.update(TABLE_CHECKLISTITEM, newValues, KEY_ITEM_ID+"=?", new String[] { idAsString }) > 0;

		if(success && item instanceof Check) {
			// Check if this checklist is now complete
			// Find the checklist ID
			Cursor c = mDb.query(TABLE_CHECKLISTITEM,
					new String[] { KEY_CHECKLISTITEM_CHECKLISTID },
					KEY_ITEM_ID+"=?", new String[] { idAsString },
					null, null, null);
			c.moveToFirst();
			Long checklist_id = c.getLong(c.getColumnIndexOrThrow(KEY_CHECKLISTITEM_CHECKLISTID));
			// Get a cursor of all check items with a blank state.
			Cursor d = mDb.query(TABLE_CHECKLISTITEM,
					new String[] { KEY_ITEM_ID },
					KEY_CHECKLISTITEM_CHECKLISTID+"=? AND "+KEY_ITEM_TYPE+"=? AND "+
					KEY_CHECKLISTITEM_STATE+"=?",
					new String[] {
						checklist_id.toString(),
						new Integer(ITEM_TYPE_CHECK).toString(),
						new Integer(Check.STATE_BLANK).toString()
					}, null, null, null);
			c.close();
			Checklist list = fetchChecklist(checklist_id);
			// If there are any items in the cursor, the checklist is not complete.
			if(d.getCount() == 0) {
				list.setCompletedDate();
			} else {
				list.clearCompletedDate();
			}
			list.save(this);
			d.close();
		}

		return success;
	}

	/**
	 * Delete a template item from the database
	 * @param item_id The ID of the item to delete
	 * @return true if the operation succeeded, false otherwise
	 */
	public boolean deleteTemplateItem(long item_id) {
		String idAsStringArray[] = { (new Long(item_id)).toString() };
		return mDb.delete(TABLE_TEMPLATEITEM, KEY_ITEM_ID+"=?", idAsStringArray) > 0;
	}

	public Cursor fetchChecklists(String sort, boolean completed, long category) {
		String completefilter = KEY_CHECKLIST_WHENCOMPLETED+" ISNULL";
		if(completed) {
			completefilter = "NOT (" + completefilter + ")";
		}
		String where = category != -1? KEY_CHECKLIST_CATEGORY + "=? AND "+completefilter: completefilter;
		String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
		return mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS, where,
				args, null, null, sort);
	}

	public Cursor fetchChecklists(String sort, long category) {
		String where = category != -1? KEY_CHECKLIST_CATEGORY + "=?": "";
		String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
		return mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS, where,
				args, null, null, sort);
	}

	// TODO: write fetchChecklistsByDate after I decide how I want to group them

	public Checklist fetchChecklist(long checklist_id) {
		String checkIdString=(new Long(checklist_id)).toString();
		Cursor c = mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS,
				KEY_CHECKLIST_ID+"=?", new String[] { checkIdString }, null,
				null, null);
		Checklist cl;
		if(c.getCount() > 0) {
			c.moveToFirst();
			cl = fetchChecklistFromCursor(c);
		} else {
			cl = null;
		}
		c.close();
		return cl;
	}
	
	public Checklist fetchChecklist(String name, Date startDate) {
		Cursor c = mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS,
				KEY_CHECKLIST_NAME+"=? AND "+KEY_CHECKLIST_WHENSTARTED+"=?",
				new String[] { name, mDateFormatGMT.format(startDate) }, null,
				null, null);
		Checklist cl;
		if(c.getCount() > 0) {
			c.moveToFirst();
			cl = fetchChecklistFromCursor(c);
		} else {
			cl = null;
		}
		c.close();
		return cl;
	}
	
	public Checklist fetchChecklistFromCursor(Cursor c) {
		// Now find out what category this checklist belongs to
		int catIdx = c.getColumnIndexOrThrow(KEY_CHECKLIST_CATEGORY);
		int catId = c.getInt(catIdx);
		Category cat = fetchCategory(catId);

		Date startDate = null, completedDate = null;
		try {
			String startDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_WHENSTARTED));
			startDate=startDateString == null? null: mDateFormatGMT.parse(startDateString);
			String completedDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_WHENCOMPLETED));
			completedDate=completedDateString == null? null: mDateFormatGMT.parse(completedDateString);
		} catch(ParseException e) {
			// If this ever happens it's due to mDateFormat not matching up with the
			// field constraints.
		}
		Checklist cl = new Checklist(c.getLong(c.getColumnIndexOrThrow(KEY_CHECKLIST_ID)), cat,
				c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_NAME)),
				c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_TEMPLATENAME)),
				c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_TEMPLATEVERSION)),
				startDate,
				completedDate
		);
		return cl;
	}
	
	protected ContentValues getChecklistValues(Checklist c) {
		ContentValues v = new ContentValues();
		String name = c.getName();
		if(name.equals("")) {
			return null;
		}
		v.put(KEY_CHECKLIST_NAME, name);
		// TODO remove for Database v6
		if(c.getCategory() == null) {
			v.put(KEY_CHECKLIST_CATEGORY, KEY_CATEGORY_ID_UNFILED);
		} else {
			v.put(KEY_CHECKLIST_CATEGORY, c.getCategory().getID());
		}
		Date start = c.getStartDate(), completed = c.getCompletedDate();
		if(start != null) {
			v.put(KEY_CHECKLIST_WHENSTARTED, mDateFormatGMT.format(start));
		} else {
			v.putNull(KEY_CHECKLIST_WHENSTARTED);
		}
		if(completed != null) {
			v.put(KEY_CHECKLIST_WHENCOMPLETED, mDateFormatGMT.format(completed));
		} else {
			v.putNull(KEY_CHECKLIST_WHENCOMPLETED);
		}
		v.put(KEY_CHECKLIST_TEMPLATENAME, c.getTemplateName());
		v.put(KEY_CHECKLIST_TEMPLATEVERSION, c.getVersion());
		
		return v;
	}
	
	/**
	 * Add a new checklist to the database.
	 * @param c The Checklist object to populate the new record with
	 * @return The ID of the new checklist record or -1 if failed
	 */
	public long createChecklist(Checklist c) {
		ContentValues initialValues = getChecklistValues(c);
		if(initialValues == null) {
			return -1;
		}
		// Assigning an ID is only used when initially populating the database
		if(c.getID() != -1) {
			initialValues.put(KEY_CHECKLIST_ID, c.getID());
		}

		return mDb.insert(TABLE_CHECKLIST, null, initialValues);
	}
	
	/**
	 * Update a Checklist record based on the current values in the object
	 * @param c The Checklist object to update in the database
	 * @return true if the checklist was successfully updated, false otherwise
	 */
	public boolean updateChecklist(Checklist c) {
		ContentValues newValues = getChecklistValues(c);
		if(newValues == null) {
			return false;
		}
		String idAsString=(new Long(c.getID())).toString();
		
		return mDb.update(TABLE_CHECKLIST, newValues, KEY_CHECKLIST_ID+"=?", new String[] { idAsString }) > 0;
	}

	/**
	 * Delete a Checklist and all its items from the database
	 * @param checklist_id The ID of the checklist to delete 
	 * @return True if the operation succeeded, false otherwise
	 */
	public boolean deleteChecklist(long checklist_id) {
		String idAsStringArray[] = { (new Long(checklist_id)).toString() };
		// First delete all items belonging to this template
		mDb.delete(TABLE_CHECKLISTITEM, KEY_CHECKLISTITEM_CHECKLISTID+"=?", idAsStringArray);

		return mDb.delete(TABLE_CHECKLIST, KEY_CHECKLIST_ID+"=?", idAsStringArray) > 0;
	}

	public Checklist createChecklistFromTemplate(long template_id) {
		Template t = fetchTemplate(template_id);
		Checklist c = new Checklist(t.getCategory(),
				t.getName()+" ("+DateFormat.getDateInstance(DateFormat.SHORT).format(new Date())+")");
		c.save(this);
		Cursor template_items = fetchItemsAsCursor(ITEM_CONTAINER_TEMPLATE, template_id);
		if(template_items != null) {
			template_items.moveToFirst();
			do {
				Item i = fetchItemFromCursor(template_items);
				Item newi;
				if(i instanceof Check) {
					newi = new Check(ITEM_CONTAINER_CHECKLIST, c.getID());
					((Check)newi).setState(Check.STATE_BLANK);
				} else {
					newi = new Note(ITEM_CONTAINER_CHECKLIST, c.getID());
				}
				newi.setOrder(i.getOrder());
				newi.setText(i.getText());
				newi.save(this);
			} while(template_items.moveToNext());
			template_items.close();
		}
		return c;
	}
}